﻿Imports System.Net
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports System.Xml.XPath
Imports System.Diagnostics
Imports System.IO

Partial Class Utils_ExportProdukt
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("DataHorejsi").ConnectionString)
    con.Open()

    'nacteni obrazku v adresari XML/Images do pomocne tabulky
    Dim directory As DirectoryInfo = New DirectoryInfo(Server.MapPath("~/XML/Images"))

    Dim cmdCommand As New SqlCommand("CREATE TABLE #myFileList (FileNumber INT IDENTITY,FileName VARCHAR(256))", con)
    cmdCommand.ExecuteNonQuery()

    For Each fi As FileInfo In directory.GetFiles()
      cmdCommand = New SqlCommand("INSERT INTO #myFileList VALUES ('" + fi.Name + "')", con)
      cmdCommand.ExecuteNonQuery()
    Next


    Dim cmdProducer As New SqlCommand("select id_vyrobce as [ID],nazev as [Name] from T_Vyrobce for xml path('PRODUCER'), root('PRODUCERS')", con)

    Dim cmdMenu As New SqlCommand("select distinct parent.ID_kategorie as ID,parent.Text_CZ as [Text],child.ID_subkategorie as ID,child.Text_CZ as [Text] from " _
                            & "T_Produkt p left join T_Kategorie parent on p.ID_kategorie=parent.ID_kategorie " _
                            & "left join T_Subkategorie child on p.ID_subkategorie=child.ID_subkategorie " _
                            & "where(Parent.ID_kategorie <> 0) for xml auto, elements, root('MENU')", con)





        Dim cmdProduct As New SqlCommand("select rtrim(ID_objcislo) as PRODUCT_CODE,k.ID_kategorie as PARENT_ID, s.ID_subkategorie as CHILD_ID,p.Text_CZ as PRODUCT,ID_vyrobce as PRODUCER_ID," _
                                        & " '<![CDATA[' + isnull(p.Detail,'') + ']]>' as DESCRIPTION_FULL,p.VC as PRICE,p.MC as PRICE_MC, p.Marze as MARGIN,DPH as VAT," _
                                        & "p.Skladem as AVAILABILITY," _
                                        & "CASE when #myFileList.filename is null then '' else  'http://www.horejsi.cz/Images/' + ltrim(rtrim(ID_objcislo)) + '.jpg' END as IMGURL " _
                                        & "from T_Produkt p left join T_Kategorie k on p.ID_kategorie=k.ID_kategorie " _
                                        & "left join T_Subkategorie s on p.ID_subkategorie= s.ID_subkategorie " _
                                        & "left outer join #myFileList on rtrim(id_objcislo) + '.jpg' = #myFileList.filename " _
                                        & "where(Zobrazit = 1) for xml path('SHOPITEM'),root('SHOP')", con)

    Dim producerDataSet As DataSet = New DataSet()
    producerDataSet.ReadXml(cmdProducer.ExecuteXmlReader())
    producerDataSet.WriteXml(Server.MapPath("..\XML\producer.xml"))


    Dim menuDataSet As DataSet = New DataSet()
    menuDataSet.ReadXml(cmdMenu.ExecuteXmlReader())
    menuDataSet.WriteXml(Server.MapPath("..\XML\menu.xml"))



    Dim productDataSet As DataSet = New DataSet()
    productDataSet.ReadXml(cmdProduct.ExecuteXmlReader())
    productDataSet.WriteXml(Server.MapPath("..\XML\product.xml"))

    cmdCommand = New SqlCommand("DROP TABLE #myFileList", con)
    cmdCommand.ExecuteNonQuery()

    con.Close()

    uploadFileUsingFTP("ftp://rchorejsi@rc-profimodel.cz/producer.xml", Server.MapPath("..\XML\producer.xml"), "rchorejsi", "uploadXML062010")
    uploadFileUsingFTP("ftp://rchorejsi@rc-profimodel.cz/menu.xml", Server.MapPath("..\XML\menu.xml"), "rchorejsi", "uploadXML062010")
    uploadFileUsingFTP("ftp://rchorejsi@rc-profimodel.cz/product.xml", Server.MapPath("..\XML\product.xml"), "rchorejsi", "uploadXML062010")

    copyImages()
    createXMLFiles()

  End Sub

  Private Sub createXMLFIles()
    Dim Trgt As StreamWriter
    Trgt = New StreamWriter(MapPath("..\XML\index.xml"))
    Trgt.WriteLine("<?xml version=""1.0"" encoding=""utf-8""?> ")
    Trgt.WriteLine("<FileInfo>")
    Trgt.WriteLine("<Author>Hořejší model s.r.o.</Author>")
    Trgt.WriteLine("<CreationDate>" & DateTime.Now & "</CreationDate>")
    ScanFolder(MapPath("..\XML\Images"), Trgt)
    Trgt.WriteLine("</FileInfo>")
    Trgt.Close()
  End Sub

  Private Sub ScanFolder(ByVal FldName As String, ByVal trgt As StreamWriter)
    'On Error GoTo NextSub
    Dim S As String

    Dim TotFiles, TotFolders, TotSize As Long
    Dim sZ As Double
    Dim Fld As New DirectoryInfo(FldName), Fl As FileInfo
    Dim MainFld As New DirectoryInfo(FldName)
    trgt.WriteLine("<Folder value=""" & ParseXML(MainFld.Name) & """>")
    For Each Fld In MainFld.GetDirectories
      ScanFolder(Fld.FullName(), trgt)
      TotFolders += 1
    Next
    For Each Fl In MainFld.GetFiles
      TotFiles += 1
      S = Fl.FullName
      If Fl.Name <> "index.xml" Then
        sZ = Fl.Length
        trgt.WriteLine(("<File Name='" & ParseXML(Fl.Name) & "' Size='" & sZ & "' Modified='" & Format(Fl.LastWriteTime, "dd.MM.yyyy HH:mm:ss") & "' IMGURL='http://www.horejsi.cz/Images/" & Fl.Name & "'></File>").Replace("'", """"))
        TotSize += Fl.Length
      End If
    Next
    trgt.WriteLine(("<Summary FolderName='" & ParseXML(MainFld.Name) & "'  Files='" & TotFiles & "' Folders='" & TotFolders & "' Size='" & TotSize & "'></Summary>").Replace("'", """"))
    trgt.WriteLine("</Folder>")
NextSub:
  End Sub

  Function ParseXML(ByVal S As String) As String
    S = S.Replace("&", "&")
    S = S.Replace("'", "")
    S = S.Replace("""", "")
    Return S
  End Function

  Private Sub copyImages()
    Dim sourceDir As String = MapPath("..\Images")
    Dim targetDir As String = MapPath("..\XML\Images")

    Try
      Dim picList As String() = Directory.GetFiles(sourceDir, "*.jpg")

      ' Copy picture files. 
      For Each f As String In picList
        Dim fiSource As FileInfo = New FileInfo(f)
        Dim fName As String = f.Substring(sourceDir.Length + 1)

        Dim copyIt As Boolean = False
        If File.Exists(Path.Combine(targetDir, fName)) Then
          Dim fiTarget As FileInfo = New FileInfo(Path.Combine(targetDir, fName))
          If fiSource.LastWriteTimeUtc > fiTarget.LastWriteTimeUtc Then 'file in the source directory is newer than file in the target directory
            copyIt = True
          End If
        Else
          copyIt = True
        End If
        'LastWriteTime()
        'Length()

        If copyIt Then
          File.Copy(Path.Combine(sourceDir, fName), Path.Combine(targetDir, fName), True)
        End If
      Next

    Catch dirNotFound As DirectoryNotFoundException
      Console.WriteLine(dirNotFound.Message)
    End Try
  End Sub

  Public Sub uploadFileUsingFTP(ByVal CompleteFTPPath As String, ByVal CompleteLocalPath As String, Optional ByVal UName As String = "", Optional ByVal PWD As String = "")

    'Create a FTP Request Object and Specfiy a Complete Path
    Dim reqObj As FtpWebRequest = WebRequest.Create(CompleteFTPPath)

    'Call A FileUpload Method of FTP Request Object
    reqObj.Method = WebRequestMethods.Ftp.UploadFile

    'If you want to access Resourse Protected You need to give User Name      and PWD
    reqObj.Credentials = New NetworkCredential(UName, PWD)

    'FileStream object read file from Local Drive
    Dim streamObj As FileStream = File.OpenRead(CompleteLocalPath)

    'Store File in Buffer
    Dim buffer(streamObj.Length) As Byte

    'Read File from Buffer
    streamObj.Read(buffer, 0, buffer.Length)

    'Close FileStream Object Set its Value to nothing
    streamObj.Close()
    streamObj = Nothing

    'Upload File to ftp://localHost/ set its object to nothing
    reqObj.GetRequestStream().Write(buffer, 0, buffer.Length)
    reqObj = Nothing

  End Sub
End Class
